package homework_2;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class Excel {
	
	
		public static void main(String[] args)throws IOException {
			InputStream in = new FileInputStream("src/Input.xls");
			HSSFWorkbook workbook = new HSSFWorkbook(in);
			HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFRow row =sheet.getRow(1);
			HSSFCell cell = row.createCell(8);
			readCell(sheet);				//读取并输出单元格信息至控制台

			changeAlignment(workbook);		//改变对齐方式,旋转对数值型无效
			setFormat(workbook);			//改变单元格格式
			setFormula(sheet);				//设置公式
			mergeCell(sheet);				//合并姓名和学号单元格，显示为姓名


			
			//输出文件到src文件夹中
			String filePath="src/Output.xls";
			FileOutputStream out = new FileOutputStream(filePath);
			workbook.write(out);
			out.close();
			}
		
		public static void readCell(HSSFSheet sheet) {
				//输出第二行的7个单元格
				HSSFRow row = sheet.getRow(1);
				HSSFCell cell= row.getCell(0);	//内容为String，getNumericCellValue
				System.out.println(cell.getStringCellValue());
				for(int j=1;j<8;j++){			//后面内容均为数字，getNumericCellValue
				HSSFCell cell_=row.getCell(j);	
				System.out.println(cell_.getNumericCellValue());
			}				
		}
		public static void setFormat(HSSFWorkbook workbook){
			//使成绩保留两位小数
			HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFDataFormat temp = workbook.createDataFormat(); 
			HSSFCellStyle style=workbook.createCellStyle();
			style.setDataFormat(temp.getFormat("#,#0.00"));
			HSSFRow row = sheet.getRow(1);
			for(int i =2;i<9;i++){
				
				HSSFCell cell = row.getCell(i);
				cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(style);
			}	
		}				
		
		//把前两列含有数据的合并
		public static void mergeCell(HSSFSheet sheet){
			CellRangeAddress region = new CellRangeAddress(0,0,0,1);
			sheet.addMergedRegion(region);
			region = new CellRangeAddress(1,1,0,1);
			sheet.addMergedRegion(region);
		}
		
		//改变对齐方式
		public static void changeAlignment(HSSFWorkbook workbook){
			HSSFCellStyle style=workbook.createCellStyle();
			HSSFSheet sheet = workbook.getSheetAt(0);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
			style.setRotation((short)20);//文本旋转20度
			for(int i =0;i<2;i++){
				HSSFRow row = sheet.getRow(i);
				for(int j=0;j<9;j++){
					HSSFCell cell=row.getCell(j);
					cell.setCellStyle(style);
					}
				}
		}
		//设置总分公式为6门成绩的和
		public static void setFormula(HSSFSheet sheet){
			HSSFRow row = sheet.getRow(1);
			HSSFCell cell = row.getCell(8);
			cell.setCellFormula("sum(C2:H2)");//因为合并了单元格所以从B1开始
		}
		
		
		
		
		
		
		
		
		
		
	}










